define SOURCE_SCHEMA = "'SIGMAHOM11A'"

define DEST_SCHEMA = "'SIGMADEV'"

alter session set optimizer_features_enable='10.2.0.1';
alter session enable parallel dml;

SELECT 'TRUNCATE TABLE ' || dest_schema || '.' || table_name || ';' || chr(10) ||
       'INSERT  /*+ PARALLEL */  INTO ' || dest_schema || '.' || table_name || '(' || columns || ')' || chr(10) ||
       'SELECT ' || columns || ' FROM ' || src_schema || '.' || table_name || ';' || chr(10) ||
       'COMMIT;'
FROM
(select src_schema, dest_schema, table_name, 
        LTRIM(MAX(SYS_CONNECT_BY_PATH(column_name,',')) KEEP (DENSE_RANK LAST ORDER BY curr),',') AS columns
      from (SELECT dtsrc.owner as src_schema,
                   dtdest.owner as dest_schema,
                   dtsrc.table_name,
                   dtsrc.column_name,
                   ROW_NUMBER() OVER (PARTITION BY dtsrc.table_name ORDER BY dtsrc.column_id) AS curr,
                   ROW_NUMBER() OVER (PARTITION BY dtsrc.table_name ORDER BY dtsrc.column_id) -1 AS prev
            FROM  dba_tab_columns dtsrc, dba_tab_columns dtdest
            WHERE dtsrc.owner = &SOURCE_SCHEMA
              AND dtdest.owner = &DEST_SCHEMA
              AND dtsrc.table_name = dtdest.table_name
              AND dtsrc.column_name = dtdest.column_name)
    GROUP BY src_schema, dest_schema, table_name
    CONNECT BY prev = PRIOR curr AND table_name = PRIOR table_name
    START WITH curr = 1) src
ORDER BY table_name;
